﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using LitJson;
using NPOI.HSSF.UserModel;
using NPOI.OpenXml4Net.OPC;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace ExcelConverter
{
    [Flags]
    public enum EConvertType
    {
        Client = 1 << 0,
        Server = 1 << 1,
    }
    public static class Converter
    {
        public static readonly char[] ARR_NEATING_SPLIT_CHARS = {'#'};
        public static readonly char[] COMMON_ARR_SPLIT_CHARS = {',', '|', '\n'};
        public static readonly char[] STRING_ARR_SPLIT_CHARS = {'|'};

        private static Encoding s_encoding = new UTF8Encoding(false);
        public static async Task<string> Convert(string filePath, string destPath, EConvertType convertType, Dictionary<string, string> prevDefineMap, 
            Action<string, int, int> OnLineChanged)
        {
            var workbook = GetWorkbook(filePath, out var fileName);

            var defineMap = prevDefineMap != null ? new Dictionary<string, string>(prevDefineMap) : new Dictionary<string, string>();
            
            var defineSheetIndex = workbook.GetSheetIndex("define");
            //纪录别名
            if(defineSheetIndex != -1)
            {
                var defineSheet = workbook.GetSheetAt(defineSheetIndex);
                for (int i = defineSheet.LastRowNum; i >= 0; i--)
                {
                    var row = defineSheet.GetRow(i);
                    if (row == null) continue;
                    for (int j = row.LastCellNum - 1; j >= 0; j--)
                    {
                        var cell = row.GetCell(j);
                        if (cell == null || string.IsNullOrEmpty(cell.StringCellValue)) continue;
                        var cellValue = cell.StringCellValue;
                        var colonIndex = cellValue.IndexOf(':');
                        if (colonIndex == -1)
                        {
                            colonIndex = cellValue.IndexOf('：');
                            if (colonIndex == -1)
                            {
                                return $"发现无效别名: {cellValue}\n{fileName}/{defineSheet.SheetName}/第{i + 1}行/第{j + 1}列";
                            }
                        }
                        var define = cellValue.Substring(0, colonIndex);
                        var replacement = cellValue.Substring(colonIndex + 1, cellValue.Length - colonIndex - 1);
                        if (defineMap.ContainsKey(define))
                        {
                            return $"发现重复的别名key: {define}\n{fileName}/{defineSheet.SheetName}/第{i + 1}行/第{j + 1}列";
                        }
                        defineMap.Add(define, replacement);
                    }
                }
            }

            var validSheetList = new List<ISheet>();
            var validSheetNameList = new List<(string, string)>();
            for (int sheetIndex = workbook.NumberOfSheets - 1; sheetIndex >= 0; sheetIndex--)
            {
                if (sheetIndex == defineSheetIndex) continue;
                var sheet = workbook.GetSheetAt(sheetIndex);
                var sheetName = sheet.SheetName.Trim();
                if (!sheetName.StartsWith("@")) continue;
                if (sheet.PhysicalNumberOfRows < 4) continue;
                //判断转换类型
                var sheetConvertTypeIndex = sheetName.LastIndexOf('#');
                if (sheetConvertTypeIndex != -1)
                {
                    var sheetConvertTypeStr = sheetName.Substring(sheetConvertTypeIndex + 1, sheetName.Length - sheetConvertTypeIndex - 1);
                    EConvertType sheetConvertType = 0;
                    if (sheetConvertTypeStr.Contains("c")) sheetConvertType |= EConvertType.Client;
                    if (sheetConvertTypeStr.Contains("s")) sheetConvertType |= EConvertType.Server;
                    //判断忽略
                    if ((sheetConvertType & convertType) == 0)
                    {
                        continue;
                    }
                    sheetName = sheetName.Substring(0, sheetConvertTypeIndex);
                }
                string prototypeName = null;
                //判断继承表格
                var prototypeIndex = sheetName.LastIndexOf('=');
                if (prototypeIndex != -1)
                {
                    var prototypeIndexStr = sheetName.Substring(prototypeIndex + 1, sheetName.Length - prototypeIndex - 1);
                    if (defineMap.TryGetValue(prototypeIndexStr, out var nameValue))
                    {
                        prototypeName = nameValue;
                    }
                    sheetName = sheetName.Substring(0, prototypeIndex);
                }
                //移除前缀
                sheetName = sheetName.Remove(0, 1);
                validSheetList.Add(sheet);
                validSheetNameList.Add((sheetName, prototypeName));
            }
            for (int sheetIndex = validSheetList.Count - 1; sheetIndex >= 0; sheetIndex--)
            {
                var sheet = validSheetList[sheetIndex];
                (string sheetName, string prototypeName) = validSheetNameList[sheetIndex];
                
                //缓存变量名和值
                var varList = new List<string>();
                var varTypeDict = new Dictionary<int, object>();
                var varTimeOffsetDict = new Dictionary<int, int>();
                var varDefinedSet = new HashSet<int>();
                var idCellIndex = -1;
                //遍历变量名
                #region 缓存变量名
                {
                    var varRow = sheet.GetRow(0);
                    var varConvertTypeRow = sheet.GetRow(1);
                    var lastCellNum = varRow.LastCellNum;
                    for (int cellIndex = 0; cellIndex < lastCellNum; cellIndex++)
                    {
                        var cell = varRow.GetCell(cellIndex);
                        //纪录无效列
                        if (cell == null || cell.CellType != CellType.String)
                        {
                            varList.Add(null);
                            continue;
                        }
                        //转换类型
                        EConvertType varConvertType = 0;
                        var convertTypeCell = varConvertTypeRow.GetCell(cellIndex);
                        if (convertTypeCell == null || cell.CellType != CellType.String)
                        {
                            varConvertType = EConvertType.Client | EConvertType.Server;
                        }
                        else
                        {
                            var varConvertTypeStr = convertTypeCell.StringCellValue.Trim();
                            if (varConvertTypeStr.Contains("c")) varConvertType |= EConvertType.Client;
                            if (varConvertTypeStr.Contains("s")) varConvertType |= EConvertType.Server;
                        }
                        //判断忽略
                        if ((varConvertType & convertType) == 0)
                        {
                            varList.Add(null);
                            continue;
                        }
                        //缓存类型
                        var cellValue = cell.StringCellValue.Trim();
                        if (Regex.IsMatch(cellValue, ".[A-Za-z0-9+-]*_.[A-Za-z0-9]+"))
                        {
                            var type = cellValue.Substring(0, cellValue.IndexOf('_')).Trim();
                            //使用别名
                            if (type[0] == '$')
                            {
                                varDefinedSet.Add(cellIndex);
                                type = type.Remove(0, 1);
                                cellValue = cellValue.Remove(0, 1);
                            }
                            //判断时区
                            if (type[0] == 't')
                            {
                                if (type.Length == 1)
                                {
                                    varTimeOffsetDict[cellIndex] = 8; //默认东八区
                                }
                                else
                                {
                                    var offsetStr = type.Substring(1, type.Length - 1);
                                    if (int.TryParse(offsetStr, out var offset))
                                    {
                                        varTimeOffsetDict[cellIndex] = offset;
                                    }
                                    else
                                    {
                                        return $"无效类型: {type}\n{fileName}/{sheetName}";
                                    }
                                    type = "t";
                                    cellValue = cellValue.Remove(1, offsetStr.Length);
                                }
                            }
                            switch (type)
                            {
                                case "t":   varTypeDict.Add(cellIndex, default(DateTime)); break;
                                case "i":   varTypeDict.Add(cellIndex, default(int)); break;
                                case "u":   varTypeDict.Add(cellIndex, default(ulong)); break;
                                case "f":   varTypeDict.Add(cellIndex, default(double)); break;
                                case "b":   varTypeDict.Add(cellIndex, default(bool)); break;
                                case "s":   varTypeDict.Add(cellIndex, ""); break;
                                case "il":  varTypeDict.Add(cellIndex, Array.Empty<int>()); break;
                                case "ill": varTypeDict.Add(cellIndex, Array.Empty<int[]>()); break;
                                case "ul":  varTypeDict.Add(cellIndex, Array.Empty<ulong>()); break;
                                case "ull": varTypeDict.Add(cellIndex, Array.Empty<ulong[]>()); break;
                                case "fl":  varTypeDict.Add(cellIndex, Array.Empty<double>()); break;
                                case "fll": varTypeDict.Add(cellIndex, Array.Empty<double[]>()); break;
                                case "bl":  varTypeDict.Add(cellIndex, Array.Empty<bool>()); break;
                                case "bll": varTypeDict.Add(cellIndex, Array.Empty<bool[]>()); break;
                                case "sl":  varTypeDict.Add(cellIndex, Array.Empty<string>()); break;
                                case "sll": varTypeDict.Add(cellIndex, Array.Empty<string[]>()); break;
                                default:
                                {
                                    return $"无效类型: {type}\n({fileName}/{sheetName})";
                                }
                            }
                            varList.Add(cellValue);
                            //试图找到id列
                            if (cellValue == "i_id" || cellValue == "s_id")
                            {
                                idCellIndex = cellIndex;
                            } 
                        }
                        else
                        {
                            varList.Add(null);
                            continue;
                        }
                    }
                    if (idCellIndex == -1)
                    {
                        return $"请检查是否缺少id列或没有不存在cs行!\n({fileName}/{sheetName})";
                    }
                }
                #endregion
                //写入值
                var codeWriter = new CodeWriter();
                #region 写入
                {
                    var curLine = 0;
                    var curEachCount = 0;
                    var totalLine = sheet.LastRowNum - 1;
                    var changedEachCount = totalLine / 25;
                    var isPackedInherit = false;
                    var idSet = new HashSet<string>();
                    var lastRowNum = sheet.LastRowNum;
                    codeWriter.WriteExcelStart();
                    for (int rowIndex = 3; rowIndex <= lastRowNum; rowIndex++, curLine++, curEachCount++)
                    {
                        var row = sheet.GetRow(rowIndex);
                        if (row == null) continue;
                        //跳过无效id行
                        {
                            var idCell = row.GetCell(idCellIndex);
                            if (idCell == null || idCell.CellType == CellType.Blank || idCell.CellType == CellType.Unknown)
                            {
                                continue;
                            }
                        }
                        codeWriter.WriteObjectStart();
                        if (!isPackedInherit)
                        {
                            isPackedInherit = true;
                            if (!string.IsNullOrEmpty(prototypeName))
                            {
                                codeWriter.WritePropertyName("__prototype");
                                codeWriter.Write(prototypeName);
                            }
                        }
                        if (curEachCount >= changedEachCount)
                        {
                            OnLineChanged?.Invoke(sheetName, curLine, totalLine);
                            curEachCount -= changedEachCount;
                            await Task.Delay(5);
                        }
                        var count = varList.Count;
                        for (int cellIndex = 0; cellIndex < count; cellIndex++)
                        {
                            //跳过无效列
                            var varName = varList[cellIndex];
                            if (string.IsNullOrEmpty(varName)) continue;
                            //获取内容
                            var cell = row.GetCell(cellIndex);
                            var type = varTypeDict[cellIndex];
                            var varNameIsDefined = varDefinedSet.Contains(cellIndex);
                            codeWriter.WritePropertyName(varName);
                            if (cell == null || cell.CellType == CellType.Blank || cell.CellType == CellType.Unknown || 
                                ((cell.CellType == CellType.Formula ? cell.CachedFormulaResultType : cell.CellType) == CellType.String && string.IsNullOrEmpty(cell.StringCellValue)))
                            {
                                if (type is int)
                                {
                                    codeWriter.Write(default(int));
                                }
                                else if (type is ulong)
                                {
                                    codeWriter.Write(default(ulong));
                                }
                                else if (type is double)
                                {
                                    codeWriter.Write(default(double));
                                }
                                else if (type is bool)
                                {
                                    codeWriter.Write(default(bool));
                                }
                                else if (type is string)
                                {
                                    codeWriter.Write("");
                                }
                                else if (type is DateTime)
                                {
                                    codeWriter.Write(default(long));
                                }
                                else if (type is Array)
                                {
                                    codeWriter.WriteArrayStart();
                                    codeWriter.WriteArrayEnd();
                                }
                                continue;
                            }
                            switch (cell.CellType == CellType.Formula ? cell.CachedFormulaResultType : cell.CellType)
                            {
                                case CellType.Error:
                                {
                                    if (cell is XSSFCell xssfCell)
                                    {
                                        return $"发现一个错误值: {xssfCell.ErrorCellString}\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                    }
                                    else 
                                    {
                                        return $"发现一个错误值!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                    }
                                }
                                case CellType.Numeric: //数值
                                case CellType.Formula: //公式
                                {
                                    var cellValue = cell.NumericCellValue;
                                    //根据类型赋值
                                    if (type is int)
                                    {
                                        var intValue = ConverterUtils.ToInt(cellValue);
                                        codeWriter.Write(intValue);

                                        if (cellIndex == idCellIndex) //intId
                                        {
                                            if (idSet.Add(intValue.ToString()))
                                            {
                                                codeWriter.InsertId(intValue);
                                            }
                                            else
                                            {
                                                return $"发现重复的id: {intValue}\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                            }
                                        }
                                    }
                                    else if (type is ulong)
                                    {
                                        var ulongValue = ConverterUtils.ToUlong(cellValue);
                                        codeWriter.Write(ulongValue);
                                    }
                                    else if (type is double)
                                    {
                                        codeWriter.Write(cellValue);
                                    }
                                    else if (type is DateTime)
                                    {
                                        var intValue = ConverterUtils.ToInt(cellValue);
                                        codeWriter.Write(intValue);
                                    }
                                    else if (type is bool)
                                    {
                                        codeWriter.Write(cellValue > 0.5d);
                                    }
                                    else if (type is string)
                                    {
                                        codeWriter.Write(cellValue.ToString());
                                        
                                        if (cellIndex == idCellIndex) //stringId
                                        {
                                            //强转为int
                                            var intValue = ConverterUtils.ToInt(cellValue);
                                            if (idSet.Add(intValue.ToString()))
                                            {
                                                codeWriter.InsertId(intValue.ToString());
                                            }
                                            else
                                            {
                                                return $"发现重复的id: {intValue}\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                            }
                                        }
                                    }
                                    else if (type is int[])
                                    {
                                        codeWriter.WriteArrayStart();
                                        codeWriter.Write(ConverterUtils.ToInt(cellValue));
                                        codeWriter.WriteArrayEnd();
                                    }
                                    else if (type is int[][])
                                    {
                                        codeWriter.WriteArrayStart();
                                        codeWriter.WriteArrayStart();
                                        codeWriter.Write(ConverterUtils.ToInt(cellValue));
                                        codeWriter.WriteArrayEnd();
                                        codeWriter.WriteArrayEnd();
                                    }
                                    else if (type is ulong[])
                                    {
                                        codeWriter.WriteArrayStart();
                                        codeWriter.Write(ConverterUtils.ToUlong(cellValue));
                                        codeWriter.WriteArrayEnd();
                                    }
                                    else if (type is ulong[][])
                                    {
                                        codeWriter.WriteArrayStart();
                                        codeWriter.WriteArrayStart();
                                        codeWriter.Write(ConverterUtils.ToUlong(cellValue));
                                        codeWriter.WriteArrayEnd();
                                        codeWriter.WriteArrayEnd();
                                    }
                                    else if (type is double[])
                                    {
                                        codeWriter.WriteArrayStart();
                                        codeWriter.Write(cellValue);
                                        codeWriter.WriteArrayEnd();
                                    }
                                    else if (type is double[][])
                                    {
                                        codeWriter.WriteArrayStart();
                                        codeWriter.WriteArrayStart();
                                        codeWriter.Write(cellValue);
                                        codeWriter.WriteArrayEnd();
                                        codeWriter.WriteArrayEnd();
                                    }
                                    else if (type is bool[])
                                    {
                                        codeWriter.WriteArrayStart();
                                        codeWriter.Write(cellValue > 0.5d);
                                        codeWriter.WriteArrayEnd();
                                    }
                                    else if (type is bool[][])
                                    {
                                        codeWriter.WriteArrayStart();
                                        codeWriter.WriteArrayStart();
                                        codeWriter.Write(cellValue > 0.5d);
                                        codeWriter.WriteArrayEnd();
                                        codeWriter.WriteArrayEnd();
                                    }
                                    else if (type is string[])
                                    {
                                        codeWriter.WriteArrayStart();
                                        codeWriter.Write(cellValue.ToString());
                                        codeWriter.WriteArrayEnd();
                                    }
                                    else if (type is string[][])
                                    {
                                        codeWriter.WriteArrayStart();
                                        codeWriter.WriteArrayStart();
                                        codeWriter.Write(cellValue.ToString());
                                        codeWriter.WriteArrayEnd();
                                        codeWriter.WriteArrayEnd();
                                    }
                                    break;
                                }
                                case CellType.String: //字符串
                                {
                                    var cellValue = cell.StringCellValue;
                                    //替换别名
                                    if (varNameIsDefined && !(type is int) && !(type is Array))
                                    {
                                        if (!defineMap.ContainsKey(cellValue))
                                        {
                                            return $"变量{varName}的值[{cellValue}]找不到对应的别名\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                        }
                                        cellValue = defineMap[cellValue];
                                    }
                                    //根据类型赋值
                                    if (type is int)
                                    {
                                        if (varNameIsDefined)
                                        {
                                            //包含加法
                                            if (cellValue.Contains("&"))
                                            {
                                                var addValues = cellValue.Split('&');
                                                var intValue = 0;
                                                for (var i = 0; i < addValues.Length; i++)
                                                {
                                                    var addValue = addValues[i];
                                                    if (!defineMap.ContainsKey(addValue))
                                                    {
                                                        return $"变量{varName}的值[{addValue}]找不到对应的别名\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                    }
                                                    var defineValue = defineMap[addValue];
                                                    var result = ConverterUtils.ToInt(defineValue);
                                                    if (result.success)
                                                    {
                                                        intValue += result.intValue;
                                                    }
                                                    else
                                                    {
                                                        return $"变量{varName}的值[{defineValue}]不是个正确的整数!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                    }
                                                }
                                                codeWriter.Write(intValue);
                                            }
                                            else
                                            {
                                                if (!defineMap.ContainsKey(cellValue))
                                                {
                                                    return $"变量{varName}的值[{cellValue}]找不到对应的别名\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                }
                                                var defineValue = defineMap[cellValue];
                                                var result = ConverterUtils.ToInt(defineValue);
                                                if (result.success)
                                                {
                                                    codeWriter.Write(result.intValue);
                                                    
                                                    if (cellIndex == idCellIndex) //intId
                                                    {
                                                        if (idSet.Add(result.intValue.ToString()))
                                                        {
                                                            codeWriter.InsertId(result.intValue);
                                                        }
                                                        else
                                                        {
                                                            return $"发现重复的id: {result.intValue}\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                        }
                                                    }
                                                }
                                                else
                                                {
                                                    return $"变量{varName}的值[{defineValue}]不是个正确的整数!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                }
                                            }
                                        }
                                        else
                                        {
                                            var result = ConverterUtils.ToInt(cellValue);
                                            if (result.success)
                                            {
                                                codeWriter.Write(result.intValue);
                                                
                                                if (cellIndex == idCellIndex) //intId
                                                {
                                                    if (idSet.Add(result.intValue.ToString()))
                                                    {
                                                        codeWriter.InsertId(result.intValue);
                                                    }
                                                    else
                                                    {
                                                        return $"发现重复的id: {result.intValue}\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                    }
                                                }
                                            }
                                            else
                                            {
                                                return $"变量{varName}的值[{cellValue}]不是个正确的整数!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                            }
                                        }
                                    }
                                    else if (type is ulong)
                                    {
                                        var result = ConverterUtils.ToUlong(cellValue);
                                        if (result.success)
                                        {
                                            codeWriter.Write(result.ulongValue);
                                        }
                                        else
                                        {
                                            return $"变量{varName}的值[{cellValue}]不是个正确的整数!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                        }
                                    }
                                    else if (type is double)
                                    {
                                        if (double.TryParse(cellValue, out var value))
                                        {
                                            codeWriter.Write(value);
                                        }
                                        else
                                        {
                                            return $"变量{varName}的值[{cellValue}]不是个合理的浮点数!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                        }
                                    }
                                    else if (type is DateTime)
                                    {
                                        if (int.TryParse(cellValue, out var intValue))
                                        {
                                            codeWriter.Write(intValue);
                                        }
                                        else if (DateTime.TryParse(cellValue, out var dateTime))
                                        {
                                            var offset = varTimeOffsetDict[cellIndex];
                                            codeWriter.Write(ConverterUtils.GetTimeStampByUTCTime(dateTime, offset));
                                        }
                                        else
                                        {
                                            return $"变量{varName}的值[{cellValue}]不是个合理的时间!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                        }
                                    }
                                    else if (type is bool)
                                    {
                                        codeWriter.Write(cellValue.Trim() == "1");
                                    }
                                    else if (type is string)
                                    {
                                        codeWriter.Write(cellValue);
                                        
                                        if (cellIndex == idCellIndex) //stringId
                                        {
                                            if (idSet.Add(cellValue))
                                            {
                                                codeWriter.InsertId(cellValue);
                                            }
                                            else
                                            {
                                                return $"发现重复的id: {cellValue}\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                            }
                                        }
                                    }
                                    else if (type is int[])
                                    {
                                        var result = ConverterUtils.WriteIntArray(codeWriter, cellValue, varNameIsDefined, defineMap);
                                        switch (result.result)
                                        {
                                            case ConverterUtils.EWriteResult.CouldNotFoundVarDefined:
                                            {
                                                return $"变量{varName}的值[{result.arg}]找不到对应的别名\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                            }
                                            case ConverterUtils.EWriteResult.IncorrectFormatForInt:
                                            {
                                                return $"变量{varName}的值[{result.arg}]不是个正确的整数!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                            }
                                            case ConverterUtils.EWriteResult.ContainsEmptyStr:
                                            {
                                                return $"变量{varName}的值里包含了空字符串!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                            }
                                        }
                                    }
                                    else if (type is int[][])
                                    {
                                        codeWriter.WriteArrayStart();
                                        var strings = cellValue.Split(ARR_NEATING_SPLIT_CHARS);
                                        for (var i = 0; i < strings.Length; i++)
                                        {
                                            var current = strings[i].Trim('\n');
                                            var result = ConverterUtils.WriteIntArray(codeWriter, current, varNameIsDefined, defineMap);
                                            switch (result.result)
                                            {
                                                case ConverterUtils.EWriteResult.CouldNotFoundVarDefined:
                                                {
                                                    return $"变量{varName}的值[{result.arg}]找不到对应的别名\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                }
                                                case ConverterUtils.EWriteResult.IncorrectFormatForInt:
                                                {
                                                    return $"变量{varName}的值[{result.arg}]不是个正确的整数!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                }
                                                case ConverterUtils.EWriteResult.ContainsEmptyStr:
                                                {
                                                    return $"变量{varName}的值里包含了空字符串!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                }
                                            }
                                        }
                                        codeWriter.WriteArrayEnd();
                                    }
                                    else if (type is ulong[])
                                    {
                                        var result = ConverterUtils.WriteUlongArray(codeWriter, cellValue);
                                        switch (result.result)
                                        {
                                            case ConverterUtils.EWriteResult.CouldNotFoundVarDefined:
                                            {
                                                return $"变量{varName}的值[{result.arg}]找不到对应的别名\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                            }
                                            case ConverterUtils.EWriteResult.IncorrectFormatForInt:
                                            {
                                                return $"变量{varName}的值[{result.arg}]不是个正确的整数!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                            }
                                            case ConverterUtils.EWriteResult.ContainsEmptyStr:
                                            {
                                                return $"变量{varName}的值里包含了空字符串!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                            }
                                        }
                                    }
                                    else if (type is ulong[][])
                                    {
                                        codeWriter.WriteArrayStart();
                                        var strings = cellValue.Split(ARR_NEATING_SPLIT_CHARS);
                                        for (var i = 0; i < strings.Length; i++)
                                        {
                                            var current = strings[i].Trim('\n');
                                            var result = ConverterUtils.WriteUlongArray(codeWriter, current);
                                            switch (result.result)
                                            {
                                                case ConverterUtils.EWriteResult.CouldNotFoundVarDefined:
                                                {
                                                    return $"变量{varName}的值[{result.arg}]找不到对应的别名\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                }
                                                case ConverterUtils.EWriteResult.IncorrectFormatForInt:
                                                {
                                                    return $"变量{varName}的值[{result.arg}]不是个正确的整数!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                }
                                                case ConverterUtils.EWriteResult.ContainsEmptyStr:
                                                {
                                                    return $"变量{varName}的值里包含了空字符串!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                }
                                            }
                                        }
                                        codeWriter.WriteArrayEnd();
                                    }
                                    else if (type is double[])
                                    {
                                        var result = ConverterUtils.WriteDoubleArray(codeWriter, cellValue, varNameIsDefined, defineMap);
                                        switch (result.result)
                                        {
                                            case ConverterUtils.EWriteResult.CouldNotFoundVarDefined:
                                            {
                                                return $"变量{varName}的值[{result.arg}]找不到对应的别名\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                            }
                                            case ConverterUtils.EWriteResult.IncorrectFormatForDouble:
                                            {
                                                return $"变量{varName}的值[{result.arg}]不是个合理的浮点数!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                            }
                                            case ConverterUtils.EWriteResult.ContainsEmptyStr:
                                            {
                                                return $"变量{varName}的值里包含了空字符串!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                            }
                                        }
                                    }
                                    else if (type is double[][])
                                    {
                                        codeWriter.WriteArrayStart();
                                        var strings = cellValue.Split(ARR_NEATING_SPLIT_CHARS);
                                        for (var i = 0; i < strings.Length; i++)
                                        {
                                            var current = strings[i].Trim('\n');
                                            var result = ConverterUtils.WriteDoubleArray(codeWriter, current, varNameIsDefined, defineMap);
                                            switch (result.result)
                                            {
                                                case ConverterUtils.EWriteResult.CouldNotFoundVarDefined:
                                                {
                                                    return $"变量{varName}的值[{result.arg}]找不到对应的别名\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                }
                                                case ConverterUtils.EWriteResult.IncorrectFormatForDouble:
                                                {
                                                    return $"变量{varName}的值[{result.arg}]不是个合理的浮点数!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                }
                                                case ConverterUtils.EWriteResult.ContainsEmptyStr:
                                                {
                                                    return $"变量{varName}的值里包含了空字符串!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                }
                                            }
                                        }
                                        codeWriter.WriteArrayEnd();
                                    }
                                    else if (type is bool[])
                                    {
                                        var result = ConverterUtils.WriteBoolArray(codeWriter, cellValue, varNameIsDefined, defineMap);
                                        switch (result.result)
                                        {
                                            case ConverterUtils.EWriteResult.CouldNotFoundVarDefined:
                                            {
                                                return $"变量{varName}的值[{result.arg}]找不到对应的别名\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                            }
                                            case ConverterUtils.EWriteResult.IncorrectFormatForBool:
                                            {
                                                return $"变量{varName}的值[{result.arg}]不是个合理的布尔值(0或1)!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                            }
                                            case ConverterUtils.EWriteResult.ContainsEmptyStr:
                                            {
                                                return $"变量{varName}的值里包含了空字符串!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                            }
                                        }
                                    }
                                    else if (type is bool[][])
                                    {
                                        codeWriter.WriteArrayStart();
                                        var strings = cellValue.Split(ARR_NEATING_SPLIT_CHARS);
                                        for (var i = 0; i < strings.Length; i++)
                                        {
                                            var current = strings[i].Trim('\n');
                                            var result = ConverterUtils.WriteBoolArray(codeWriter, current, varNameIsDefined, defineMap);
                                            switch (result.result)
                                            {
                                                case ConverterUtils.EWriteResult.CouldNotFoundVarDefined:
                                                {
                                                    return $"变量{varName}的值[{result.arg}]找不到对应的别名\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                }
                                                case ConverterUtils.EWriteResult.IncorrectFormatForBool:
                                                {
                                                    return $"变量{varName}的值[{result.arg}]不是个合理的布尔值(0或1)!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                }
                                                case ConverterUtils.EWriteResult.ContainsEmptyStr:
                                                {
                                                    return $"变量{varName}的值里包含了空字符串!\n({fileName}/{sheetName}/第{rowIndex + 1}行)";
                                                }
                                            }
                                        }
                                        codeWriter.WriteArrayEnd();
                                    }
                                    else if (type is string[])
                                    {
                                        ConverterUtils.WriteStringArray(codeWriter, cellValue, varNameIsDefined, defineMap);
                                    }
                                    else if (type is string[][])
                                    {
                                        codeWriter.WriteArrayStart();
                                        var strings = cellValue.Split(ARR_NEATING_SPLIT_CHARS);
                                        for (var i = 0; i < strings.Length; i++)
                                        {
                                            var current = strings[i].Trim('\n');
                                            ConverterUtils.WriteStringArray(codeWriter, current, varNameIsDefined, defineMap);
                                        }
                                        codeWriter.WriteArrayEnd();
                                    }
                                    break;
                                }
                            }
                        }
                        codeWriter.WriteObjectEnd();
                    }
                    codeWriter.WriteExcelEnd();
                }
                #endregion
                if (!defineMap.ContainsKey(sheetName))
                {
                    return $"工作表({sheetName}) 找不到对应的别名\n({fileName}/{sheetName})";
                }
                var content = codeWriter.ToString();
                content = Regex.Unescape(content);
                File.WriteAllText(Path.Combine(destPath, $"{fileName}.{defineMap[sheetName]}.json"), content, s_encoding);
            }
            return null;
        }
        
        private static IWorkbook GetWorkbook(string path, out string fileName)
        {
            IWorkbook book = null;
            if (path.EndsWith(".xls"))
            {
                var lastDivIndex = path.LastIndexOf('\\');
                fileName = path.Substring(lastDivIndex + 1, path.Length - lastDivIndex - 1 - 4);
                
                using (var fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    book = new HSSFWorkbook(fs);
                }
            }
            else if (path.EndsWith(".xlsx"))
            {
                var lastDivIndex = path.LastIndexOf('\\');
                fileName = path.Substring(lastDivIndex + 1, path.Length - lastDivIndex - 1 - 5);

                using (var fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    book = new XSSFWorkbook(OPCPackage.Open(fs, true));
                }
            }
            else
            {
                fileName = null;
            }
            return book;
        }

        public static (string reason, Dictionary<string, string> defineMap) GetDefineMap(string defineFilePath)
        {
            var defineMap = new Dictionary<string, string>();
            //专用别名表
            if (!string.IsNullOrEmpty(defineFilePath) && File.Exists(defineFilePath))
            {
                var defineFile = GetWorkbook(defineFilePath, out var defineFileName);
                var numberOfSheets = defineFile.NumberOfSheets;
                for (int sheetIndex = numberOfSheets - 1; sheetIndex >= 0; sheetIndex--)
                {
                    var defineSheet = defineFile.GetSheetAt(sheetIndex);
                    for (int i = defineSheet.LastRowNum; i >= 0; i--)
                    {
                        var row = defineSheet.GetRow(i);
                        if (row == null) continue;
                        for (int j = row.LastCellNum - 1; j >= 0; j--)
                        {
                            var cell = row.GetCell(j);
                            if (cell == null || string.IsNullOrEmpty(cell.StringCellValue)) continue;
                            var cellValue = cell.StringCellValue;
                            var colonInedx = cellValue.IndexOf(':');
                            if (colonInedx == -1)
                            {
                                return ($"发现无效别名: {cellValue}\n{defineFileName}/{defineSheet.SheetName}/第{i + 1}行/第{j + 1}列", defineMap);
                            }
                            var define = cellValue.Substring(0, colonInedx);
                            var replacement = cellValue.Substring(colonInedx + 1, cellValue.Length - colonInedx - 1);
                            if (defineMap.ContainsKey(define))
                            {
                                return ($"发现重复的别名key: {define}\n{defineFileName}/{defineSheet.SheetName}/第{i + 1}行/第{j + 1}列", defineMap);
                            }
                            defineMap.Add(define, replacement);
                        }
                    }
                }
            }
            return (null, defineMap);
        }
    }
}